Source Layer
The Anthology Student and/or CampusNexus CRM databases serve as the source of data throughout Student Analytics.
Day-to-day changes made to the databases (insert, update, and delete operations) are captured using the Change Data Capture feature of SQL Server 2016/2017 releases.
Anthology Student
To track changes to the required Anthology Student database source tables, change data capture is further enabled for these individual tables with specific lists of columns to be captured. This, in turn, creates new change data capture metadata tables under a new database schema ‘cdc’, called ‘change tables’, for each of the source tables. The change tables serve as a repository for the changes that occur to the data in the individual source tables, supporting incremental updates to the Data Warehouse. Upon upgrading an Anthology Student database with Student Analytics, an additional database file group ‘C2000_FG_CDC_Analytics’ and database file are created to contain the change tables.
Tables Enabled for Change Data Capture
Refer to Anthology Student Tables and Columns enabled for CDC.xlsx for a detailed list of Anthology Student source tables along with the only subset of columns that needed to be tracked. For performance reasons, not all columns are tracked.
NOTE: Prior to release of Student Analytics 5.5.2, the installation process involved a verification step to disable Change Data Capture on columns that were not part of the Student Analytics configuration. The introduction of 5.5.2 prevents the disabling of Change Data Capture on columns that are not included in the Student Analytics configuration.
CampusNexus CRM
To track changes to the required CampusNexus CRM database source tables, Change Tracking is further enabled for tables that are being tracked. Change Tracking functions are used to obtain information about the changes.
Note: The Higher Education Foundation (Higher Ed) setup must be installed on the CampusNexus CRM environment. Campaign Support for the Lead object must be installed prior to the installation of CRM Analytics.
Objects and Related Tables Enabled for Change Tracking
The tables of the following CampusNexus CRM objects are used as source tables for the Data Warehouse to support the current model.
| ObjectName | TableName | ObjectName | TableName |
|---|---|---|---|
| Area of Interest | tblObjectType20018 | Education Level | tblObjectType20016 |
| Area of Study | tblObjectType20023 | Enumerations | tblEnum |
| Campaign | tblCampaignMain | tblEnumLangName | |
| tblCampaignAction | Ethnic Group | tblObjectType20029 | |
| tblURLClickStatus | Lead | tblObjectType20005 | |
| tblMailOpenRecord | tblObjectType20005_x | ||
| tblOBMReportMailer | Lead Source | tblObjectType20015 | |
| tblSMSCampaignDetails | Lead Type | tblObjectType20021 | |
| tblSMSDetails | Mailer | tblOutBoundMailer | |
| tblSMSReport | Nationality | tblObjectType20030 | |
| tblCampaignTarget | Program | tblObjectType20008 | |
| tblURL | Program Level | tblObjectType20020 | |
| tblTrackableURLClickRecord | Program Version | tblObjectType20009 | |
| Contact | tblCustomer | tblObjectType20009_x | |
| tblCustomer_SisConnector | SIS User | tblObjectType20036 | |
| tblObjectType3_x | State | tblObjectType20017 | |
| Country | tblObjectType20014 | Team | tblTeam |
| Dependency | tblDependency | Term | tblObjectType20007 |
Note: The “_x” suffixed to a table name indicates one or more underlying secondary tables of an Object.
SQL Server Agent Jobs
Databases that are “change data capture enabled” are typically associated with two SQL Server Agent jobs: one to populate the change tables (i.e., the “capture” job), the other to be responsible for change table cleanup (the “cleanup” job). Both the capture and cleanup jobs are created using default parameters when the Change Data Capture feature is enabled.
-
The capture job is started immediately. It runs continuously, processing a maximum of 1000 transactions per scan cycle with a 5-second waiting period between cycles.
-
The cleanup job runs daily at 2 a.m. It retains change table entries for 4320 minutes (3 days), removing a maximum of 5000 entries with a single delete statement.
For additional information about SQL Server Agent jobs, refer to the MSDN Library topic Change Data Capture Agent Jobs.
